package no.gnet.s10.broker;

import java.io.File;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Vector;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

import no.gnet.s10.types.Appearance;
import no.gnet.s10.types.Booking;
import no.gnet.s10.types.Comment;
import no.gnet.s10.types.Location;
import no.gnet.s10.types.Player;
import no.gnet.s10.types.Training;
import no.gnet.util.Constants;
import no.gnet.util.S10Props;

/**
 * @author Gaute
 *
 */
public class Broker {

	private static final String SELECT_APPEARANCE_FOR_TRAINING = "SELECT a.training_id, a.player_id, a.id, p.id as pid, p.name,p.handle FROM appearance a, player p where p.id=a.player_id and training_id=?";

	public static final String BOOKINGS_FOR_TRAINING_SQL = "SELECT b.id, b.action, l.name as location_name, t.dato, t.location_id ,p.handle,p.id as pid FROM location l, booking b,training t, player p where b.training_id=t.id and b.player_id=p.id and b.action<3 and t.location_id = l.id and training_id=?";

	public static final String GETNumberOfTrainingsForPlayer_SQL = "SELECT count(b.id) as bid FROM booking b, player p where b.player_id=p.id and b.player_id=? and b.action=1 and p.activated=1";

	DataSource _datasource;
	
	ConnectionProvider connprovider;

	private static Broker instance;

	public static Broker getBroker() throws Exception {
		if (instance == null) {
			instance = new Broker();
		}
		return instance;
	}

	/**
	 * @param name
	 * @param description
	 * @throws SQLException
	 */
	public int createTraining(java.sql.Date date, int location)
			throws SQLException {
		PreparedStatement pstmt = null;
		Connection conn = null;
		ResultSet rs = null;
		try {
			String createtraining = "insert into training (dato,location_id) values(?,?)";
			conn = getConnection();
			pstmt = conn.prepareStatement(createtraining);
			pstmt.setDate(1, date);
			pstmt.setInt(2, location);
			pstmt.executeUpdate();
			rs = conn.createStatement()
					.executeQuery("SELECT LAST_INSERT_ID( )");
			rs.next();
			return rs.getInt(1);
		} finally {
			if (pstmt != null)
				pstmt.close();
			if (conn != null)
				closeConnection(conn);
		}
	}

	/**
	 * @param pid
	 * @throws SQLException
	 */
	public void activatePlayer(int pid) throws SQLException {
		PreparedStatement pstmt = null;
		Connection conn = null;
		try {
			String activatesql = "update player set activated=1 where id=?";
			conn = getConnection();
			pstmt = conn.prepareStatement(activatesql);
			pstmt.setInt(1, pid);
			pstmt.executeUpdate();
		} finally {
			if (pstmt != null)
				pstmt.close();
			if (conn != null)
				closeConnection(conn);
		}
	}

	/**
	 * @param handle
	 * @param name
	 * @param pw
	 * @param email
	 * @return
	 * @throws SQLException
	 */
	public int createPlayer(String handle, String name, String pw, String email)
			throws SQLException {
		PreparedStatement pstmt = null;
		Connection conn = null;
		ResultSet rs = null;
		int playerid = -1;
		try {

			String createtraining = "insert into player(handle,name,pw,email) values (?,?,?,?)";
			conn = getConnection();
			pstmt = conn.prepareStatement(createtraining);
			pstmt.setString(1, handle);
			pstmt.setString(2, name);
			pstmt.setString(3, pw);
			pstmt.setString(4, email);
			pstmt.executeUpdate();
			rs = conn.createStatement()
					.executeQuery("SELECT LAST_INSERT_ID( )");
			rs.next();
			playerid = rs.getInt(1);

			//			Role
			String updaterole = "insert into role(handle,role_name) values (?,?)";
			pstmt = conn.prepareStatement(updaterole);
			pstmt.setString(1, handle);
			pstmt.setString(2, "s10");
			pstmt.executeUpdate();

		} finally {
			if (rs != null)
				rs.close();
			if (pstmt != null)
				pstmt.close();
			if (conn != null)
				closeConnection(conn);
		}
		return playerid;
	}

	/**
	 * @param name
	 * @param description
	 * @throws SQLException
	 */
	public void bookPlayer(int training_id, int player_id, int book)
			throws SQLException {
		Statement stmt = null;
		Connection conn = null;
		ResultSet rs = null;
		try {
			String upsertbooking = "REPLACE INTO booking (training_id,player_id,action) values ("
					+ training_id + "," + player_id + "," + book + ")";
			conn = getConnection();
			stmt = conn.createStatement();
			stmt.executeUpdate(upsertbooking);
		} finally {
			if (stmt != null)
				stmt.close();
			if (rs != null)
				rs.close();
			if (conn != null)
				closeConnection(conn);

		}
	}

	/**
	 * @param training
	 * @param player
	 * @param comment
	 * @throws SQLException
	 */
	public void createComment(int training, int player, String comment)
			throws SQLException {
		PreparedStatement pstmt = null;
		Connection conn = null;
		try {
			String sql = "insert into comment(training_id,player_id,message) values(?,?,?)";
			conn = getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1,training);
			pstmt.setInt(2, player);
			pstmt.setString(3, comment);
			pstmt.executeUpdate();
		} finally {
			if (pstmt != null)
				pstmt.close();
			if (conn != null)
				closeConnection(conn);
		}
	}

	
	
	/**
	 * @param name
	 * @param description
	 * @throws SQLException
	 */
	public void createLocation(String name, String description)
			throws SQLException {
		PreparedStatement pstmt = null;
		Connection conn = null;
		try {
			String createlocation = "insert into location (name,description) values(?,?)";
			conn = getConnection();
			pstmt = conn.prepareStatement(createlocation);
			pstmt.setString(1, name);
			pstmt.setString(2, description);
			pstmt.executeUpdate();
		} finally {
			if (pstmt != null)
				pstmt.close();
			if (conn != null)
				closeConnection(conn);
		}
	}

	/**
	 * @return
	 * @throws SQLException
	 */
	public Player[] getAllPlayers() throws SQLException {
		ResultSet rs = null;
		Statement stmt = null;
		Connection conn = null;
		Vector res = new Vector();
		try {
			String selectall = "SELECT id, handle,name,email from player";
			conn = getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(selectall);
			while (rs.next()) {
				Player player = new Player();
				player.setId(rs.getInt("id"));
				player.setHandle(rs.getString("handle"));
				player.setName(rs.getString("name"));
				player.setEmail(rs.getString("email"));
				res.add(player);
			}
		} finally {
			if (rs != null)
				rs.close();
			if (stmt != null)
				stmt.close();
			if (conn != null)
				closeConnection(conn);
		}
		Player[] arrcont = new Player[res.size()];
		res.copyInto(arrcont);
		return arrcont;
	}

	/**
	 * @return
	 * @throws SQLException
	 */
	public Player[] getAllActivePlayers() throws SQLException {
		ResultSet rs = null;
		Statement stmt = null;
		Connection conn = null;
		Vector res = new Vector();
		try {
			String selectall = "SELECT id, handle,name,email from player where activated=1";
			conn = getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(selectall);
			while (rs.next()) {
				Player player = new Player();
				player.setId(rs.getInt("id"));
				player.setHandle(rs.getString("handle"));
				player.setName(rs.getString("name"));
				player.setEmail(rs.getString("email"));
				res.add(player);
			}
		} finally {
			if (rs != null)
				rs.close();
			if (stmt != null)
				stmt.close();
			if (conn != null)
				closeConnection(conn);
		}
		Player[] arrcont = new Player[res.size()];
		res.copyInto(arrcont);
		return arrcont;
	}

	/**
	 * @param playerid
	 * @return
	 * @throws SQLException
	 */
	public Player getPlayer(int playerid) throws SQLException {
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		Connection conn = null;
		Player player = null;
		try {
			String select = "SELECT handle,name,email,activated from player where id =?";
			conn = getConnection();
			pstmt = conn.prepareStatement(select);
			pstmt.setInt(1, playerid);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				player = new Player();
				player.setId(playerid);
				player.setHandle(rs.getString("handle"));
				player.setName(rs.getString("name"));
				player.setEmail(rs.getString("email"));
				player.setActivated(rs.getInt("activated"));
			}
		} finally {
			if (rs != null)
				rs.close();
			if (pstmt != null)
				pstmt.close();
			if (conn != null)
				closeConnection(conn);
		}

		return player;
	}

	/**
	 * @param trainingid
	 * @return
	 * @throws SQLException
	 */
	public Training getTraining(int trainingid) throws SQLException {
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		Connection conn = null;
		Training training = null;
		try {
			String select = "SELECT t.dato,l.name from training t, location l where t.location_id=l.id and t.id=?";
			conn = getConnection();
			pstmt = conn.prepareStatement(select);
			pstmt.setInt(1, trainingid);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				training = new Training();
				training.setId(trainingid);
				training.setDato(rs.getDate("dato"));
				Location loc = new Location();
				loc.setName(rs.getString("name"));
				training.setLocation(loc);
			}
		} finally {
			if (rs != null)
				rs.close();
			if (pstmt != null)
				pstmt.close();
			if (conn != null)
				closeConnection(conn);
		}

		return training;
	}

	/**
	 * @param handle
	 * @return
	 * @throws SQLException
	 */
	public Player getPlayer(String handle) throws SQLException {
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		Connection conn = null;
		Player player = null;
		try {
			String select = "SELECT id,name,email,activated from player where handle =?";
			conn = getConnection();
			pstmt = conn.prepareStatement(select);
			pstmt.setString(1, handle);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				player = new Player();
				player.setId(rs.getInt("id"));
				player.setHandle(handle);
				player.setName(rs.getString("name"));
				player.setEmail(rs.getString("email"));
				player.setActivated(rs.getInt("activated"));
			}
		} finally {
			if (rs != null)
				rs.close();
			if (pstmt != null)
				pstmt.close();
			if (conn != null)
				closeConnection(conn);
		}

		return player;
	}

	/**
	 * @return
	 * @throws SQLException
	 */
	public int getNextTraining() throws SQLException {
		ResultSet rs = null;
		Statement stmt = null;
		Connection conn = null;
		int tid = -1;
		try {
			String select = "select ((dato-now())) as diff,id as tid,dato from training where (now()-dato<0) order by diff asc";
			
			conn = getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(select);
			rs.next();
			tid = (rs.getInt("tid"));
			System.out.println("next training, tid="+tid);
		} finally {
			if (rs != null)
				rs.close();
			if (stmt != null)
				stmt.close();
			if (conn != null)
				closeConnection(conn);
		}
		return tid;
	}

	/**
	 * @return
	 */
	static int countImages() {
		String images_home = S10Props.getInstance().getProps().getProperty(
				Constants.IMAGES_HOME);
		System.out.println("images_home: " + images_home);
		File f = new File(images_home);
		return f.list().length;
	}

	/**
	 * @return
	 */
	public static String getNewImgFileName() {
		return "img" + countImages() + ".jpg";
	}

	/**
	 * @return
	 * @throws SQLException
	 */
	public int getNumberOfTrainingsForPlayer(int pid) throws SQLException {
		ResultSet rs = null;
		Statement stmt = null;
		Connection conn = null;
		int tid = -1;
		try {
			String select = "SELECT count(id) as count FROM booking b where player_id="
					+ pid + " and action=1";
			conn = getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(select);
			while (rs.next()) {
				tid = (rs.getInt("count"));
			}
		} finally {
			if (rs != null)
				rs.close();
			if (stmt != null)
				stmt.close();
			if (conn != null)
				closeConnection(conn);
		}
		return tid;
	}


	/**
	 * @return
	 * @throws SQLException
	 */
	public ArrayList getCommentsForTraining(int training_id)
			throws SQLException {
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		Connection conn = null;
		ArrayList al = new ArrayList();
		try {
			String selectall = "select p.handle, c.message, c.created from comment c, player p where c.player_id=p.id and c.training_id=?";
			conn = getConnection();
			pstmt = conn.prepareStatement(selectall);
			pstmt.setInt(1, training_id);
			rs = pstmt.executeQuery();
			while (rs.next()) {	
				Comment comment = new Comment();
				comment.setPlayer_handle(rs.getString("handle"));
				comment.setMessage(rs.getString("message"));
				comment.setTimestamp(rs.getString("created"));
				al.add(comment);
			}
		}
		finally {
			if (rs != null)
				rs.close();
			if (pstmt != null)
				pstmt.close();
			if (conn != null)
				closeConnection(conn);
		}
		return al;
	}		
	
	/**
	 * @return
	 * @throws SQLException
	 */
	public Booking[] getBookingsForTraining(int training_id)
			throws SQLException {
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		Connection conn = null;
		Vector res = new Vector();
		try {
			String selectall = BOOKINGS_FOR_TRAINING_SQL;
			conn = getConnection();
			pstmt = conn.prepareStatement(selectall);
			pstmt.setInt(1, training_id);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				Booking booking = new Booking();
				booking.setId(rs.getInt("id"));
				Training training = new Training();
				training.setDato(rs.getDate("dato"));
				training.setId(training_id);
				Location location = new Location();
				location.setId(rs.getInt("location_id"));
				location.setName(rs.getString("location_name"));
				training.setLocation(location);
				Player p = new Player();
				p.setId(rs.getInt("pid"));
				p.setHandle(rs.getString("handle"));
				booking.setPlayer(p);
				booking.setTraining(training);
				booking.setAction(rs.getInt("action"));
				res.add(booking);
			}
		} finally {
			if (rs != null)
				rs.close();
			if (pstmt != null)
				pstmt.close();
			if (conn != null)
				closeConnection(conn);
		}
		Booking[] arrcont = new Booking[res.size()];
		res.copyInto(arrcont);
		return arrcont;
	}

	/**
	 * @return
	 * @throws SQLException
	 */
	public Appearance[] getAppearancesForTraining(int training_id)
			throws SQLException {
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		Connection conn = null;
		Vector res = new Vector();
		try {
			String selectall = SELECT_APPEARANCE_FOR_TRAINING;
			conn = getConnection();
			pstmt = conn.prepareStatement(selectall);
			pstmt.setInt(1, training_id);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				Appearance app = new Appearance();
				app.setId(rs.getInt("id"));
				Training t = new Training();
				t.setId(rs.getInt("training_id"));
				app.setTraining(t);
				Player p = new Player();
				p.setId(rs.getInt("pid"));
				p.setName(rs.getString("name"));
				p.setHandle(rs.getString("handle"));
				app.setPlayer(p);
				res.add(app);
			}
		} finally {
			if (rs != null)
				rs.close();
			if (pstmt != null)
				pstmt.close();
			if (conn != null)
				closeConnection(conn);
		}
		Appearance[] arrcont = new Appearance[res.size()];
		res.copyInto(arrcont);
		return arrcont;
	}

	/**
	 * @return
	 * @throws SQLException
	 */
	public Training[] getAllTrainings() throws SQLException {
		ResultSet rs = null;
		Statement stmt = null;
		Connection conn = null;
		Vector res = new Vector();
		try {
			String selectall = "select t.id, t.dato, l.name, l.id as locationid from training t, location l where t.location_id=l.id";
			conn = getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(selectall);
			while (rs.next()) {
				Training training = new Training();
				Location loc = new Location();
				loc.setName(rs.getString("name"));
				loc.setId(rs.getInt("locationid"));
				training.setId(rs.getInt("id"));
				training.setDato(rs.getDate("dato"));
				training.setLocation(loc);
				res.add(training);
			}
		} finally {
			if (rs != null)
				rs.close();
			if (stmt != null)
				stmt.close();
			if (conn != null)
				closeConnection(conn);
		}
		Training[] arrcont = new Training[res.size()];
		res.copyInto(arrcont);
		return arrcont;
	}

	/**
	 * @param contents
	 * @return
	 */
	private Object[] vectorToArray(Vector contents) {
		Object[] arrcont = new Object[contents.size()];
		contents.copyInto(arrcont);
		return arrcont;
	}

	private Broker() throws Exception {
	}

	/**
	 * @return
	 * @throws Exception
	 */
	private DataSource _getDatasource() throws Exception {
		DataSource ds = null;
		try {
			Context ctx = new InitialContext();
			if (ctx == null)
				throw new Exception("Boom - No Context");
			ds = (DataSource) ctx.lookup("java:/comp/env/jdbc/s10");
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		}
		return ds;
	}

	private Connection getConnection() throws SQLException {
		if (getConnprovider() == null) {
			try {
				_datasource = _getDatasource();
			} catch (Exception e) {
				throw new SQLException("Failed to create datasource"
						+ e.getMessage());
			}
			setConnprovider(new ConnectionProvider() {
				public Connection getConnection() throws SQLException {
					return _datasource.getConnection();
				}
			});
		}
		return getConnprovider().getConnection();
	}

	private void closeConnection(Connection conn) throws SQLException {
		if (conn != null) {
			conn.close();
		}
	}

	/**
	 * @return the connprovider
	 */
	public ConnectionProvider getConnprovider() {
		return connprovider;
	}

	/**
	 * @param connprovider the connprovider to set
	 */
	public void setConnprovider(ConnectionProvider connprovider) {
		this.connprovider = connprovider;
	}

	public Player[] getPlayersHavingMoreThanThresholdAppearances(int threshold)
			throws SQLException {
		ResultSet rs = null;
		Statement stmt = null;
		Connection conn = null;
		Vector<Player> res = new Vector<Player>();
		try {
			String selectall = "select * from (select p.id as pid,p.handle,p.name,p.email,(SELECT count(b.id) " +
					"as count FROM booking b where b.player_id=pid) as ca from player p) as a where a.ca>"
					+ threshold;
			conn = getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(selectall);
			while (rs.next()) {
				Player player = new Player();
				player.setId(rs.getInt("pid"));
				player.setHandle(rs.getString("handle"));
				player.setName(rs.getString("name"));
				player.setEmail(rs.getString("email"));
				res.add(player);
			}
		} finally {
			if (rs != null)
				rs.close();
			if (stmt != null)
				stmt.close();
			if (conn != null)
				closeConnection(conn);
		}
		Player[] arrcont = new Player[res.size()];
		res.copyInto(arrcont);
		return arrcont;

	}
}
